Cognos Reporting By Vito A. Volpe, Jr., M.S.I.S.
We have folders for
the different user groups. There is
security on the folders to restrict access to reports depending on the user’s
role.
FOMPROF (User Profile
Maintenance form) – Fund/Org security.
Below is another
variation of the Account Receivable Daily GURFEED Transaction Detail report
with more parameters for finer selection of data and is used to research
problems for internal use and for data requests from auditors.
Cash report that runs
on a schedule the first of every month.
Added a Chart of Accounts drop down so users can select ‘2’ or ‘3’ for the School of Medicine.
Javascript code added that automatically selects Fiscal Year and Fiscal Period for the user.
Sample technical documentation.
1. EXPBCNC01A – Operating Expenditures by Budget Center – Summary by Natural Classification Report
1.1 Description
The EXPBCNC01A report summarizes operating expenses by Budget Center. The intended audience for this report is
Budget Center Managers and Fiscal Administrators. We are relying on the individual user Banner
Finance Fund/Org security to display only the orgs to which they have security
to view. This is a variation on the
EXPBCNC01 requested by Mary Ann Hart where ‘Total’
in the account descriptions is removed and ‘Total Other Expenses’ are broken
out by Account Type Level 2 code.
1.2 Queries
Report_Query_1 Query – Used to obtain amounts from the Operating Ledger
view. These amounts are filtered by the
Fiscal Year and Fiscal Period Prompt values.
The amounts are also only obtained for just Fund 100 and Account Type Level 2 equal to '61','68','71','72','75','76','79'
or '81'.
Report_Query_2 Query – Used to obtain amounts from the Operating Ledger
view. These amounts are filtered by the
Fiscal Year and Fiscal Period Prompt values.
The amounts are also only obtained for just Fund 100 and Account Type Level 1 equal to '60'.
Report_Query_Final Query – Is a Union of Report_Query_1 and
Report_Query_2. A Union had to be done
to pull data from two different account levels in order to get Total Salaries
and Wages, Total Fringe Benefits and Total Other Expenses from Level 2 and
Total Personnel costs from Level 1.
FISCAL_YEAR_PROMPT – Used to populate a list box with Fiscal Year from
[List of Values].[Fiscal Year LOV].
FISCAL_PERIOD_PROMPT – Used to populate a list box with Fiscal Period
from [List of Values].[Fiscal Period LOV].
1.3 Prompts
|
Prompt Name |
Description |
Required/
Optional |
|
Fiscal Year |
Select a particular fiscal year. |
Required |
|
Fiscal Period |
Select a particular fiscal Period |
Required |
1.4 Report
Output - Detail
|
Column Name |
Description |
Source |
|
Budget Center Level |
Organization Level 4 |
Operating Ledger.ORGANIZATION_LEVEL_4 |
|
Budget Center Level Description |
Organization Level 4 Desc |
Operating Ledger.ORGANIZATION_DESC_4 |
|
Account Description |
Account Description |
Operating Ledger.ACCOUNT_DESC |
|
Original Budget |
Original Budget amount |
Operating Ledger.SUM_ADOPTED_BUDGET |
|
Revised Budget |
Revised Budget amount |
Operating Ledger.SUM_ACCUMULATED_BUDGET |
|
Current Month |
Current Month amount |
Operating Ledger.CURR_YEAR_TO_DATE_ACTIVITY |
|
YTD Actual |
YTD Actual amount |
Operating
Ledger.SUM_YEAR_TO_DATE_ACTIVITY |
|
Open Commitments Expenses |
General Encumbrance amount |
Operating Ledger.SUM_ENCUMBRANCES |
|
Budget Balance |
Revised Budget - YTD Actual - Encumbrance |
Calculated |
|
% Used |
Round((YTD Actual + Encumbrances) / Revised
Budget) |
Calculated |
1.5 Report Detail – Budget Center Summary
|
Column Name |
Description |
Source |
|
Budget Center Description |
Budget Center Description |
literal |
|
Original Budget |
Summary for the Budget Center |
Calculated |
|
Revised Budget |
Summary for the Budget Center |
Calculated |
|
Current Month |
Summary for Budget Center |
Calculated |
|
YTD Actual |
Summary for the Budget Center |
Calculated |
|
Open Commitments Expenses |
Summary for the Budget Center |
Calculated |
|
Budget Balance |
Summary for the Budget Center |
Calculated |
|
% Used |
Summary for the Budget Center |
Calculated |
1.6 Report Summary
|
Column Name |
Description |
Source |
|
Original Budget |
Summary for all Budget Centers in the
report |
Calculated |
|
Revised Budget |
Summary for all Budget Centers in the
report |
Calculated |
|
Current Month |
Summary for all Budget Centers in the
report |
Calculated |
|
YTD Actual |
Summary for all Budget Centers in the
report |
Calculated |
|
Open Commitments Expenses |
Summary for all Budget Centers in the
report |
Calculated |
|
Budget Balance |
Summary for all Budget Centers in the
report |
Calculated |
|
% Used |
ROUND(((Total(YTD Actual)+
Total(SUM_ENCUMBRANCES1)) / Total(SUM_ACCUMULATED_BUDGET)) |
Calculated |
My
Cognos 11.0.4 reports:
NOTE: Been
using Cognos since around 2011.
NOTE: Most
reports access the ODS unless there is a business reason to go against live
data like the
AR GURAPAY dump.
NOTE:
FOMPROF security used so end users can only see what Funds/Organizations they
have access to.
NOTE: I have
a report with historical expense.
NOTE: JavaScript
code put in new reports to automatically select the correct fiscal
year and
period.
NOTE: New
reports have a last page with all parameters selected by the user.
NOTE:
Started adding Chart parameter to allow for selection of medical school.
NOTE: I
have two cash reports scheduled to run.
NOTE: Use
SQL Developer to check data in PROD and ODS to do comparisons,
troubleshoot
and be sure you are using the correct data fields.
NOTE: I have created technical
documentation for all reports.
NOTE: I am
familiar with the Data Cookbook, but don’t have access to attached
my
technical
documents. That is done by someone else.
1. AR0004S - Banner
Accounts Receivable Daily GURFEED Transaction Summary
2. AR0004 - Banner Accounts
Receivable Daily GURFEED Transaction Detail
3. AR0004A_With_Name -
Banner Accounts Receivable Daily GURFEED Transaction
Detail. This has more parameters for finer selection
of data and is used to research
problems for internal
use and auditors.
4. AR0001B - Summary
Monthly Cash AR1 from Transaction History View by
Account - Subtotal by
Account
5. AR0002B - Summary
Monthly Cash PA1 from Transaction History View - Subtotal
by Account
6. FIXEDASSET01 - Fixed
Asset Report
7. EXPBC01 - Fund 100
Operating Expenditures by Budget Center
8. EXPBC02 - Fund 100
Operating Expenditures by Budget Center and Account
9. EXPBCNC01 - Fund 100
Operating Expenditures by Budget Center - Summary by
Natural Classification
10. EXPBCNC01A - Fund 100
Operating Expenditures by Budget Center - Summary
by Natural
Classification
11. EXPORG01 - Fund 100
Operating Expenditures by Budget Center and
Organization
12. EXPORG02 - Fund 100
Operating Expenditures by Budget Center, Organization
and Account
13. EXPORGNC01 - Fund 100
Operating Expenditures by Organization - Summary by
Natural
Classification
14. REVBC01 - Fund 100
Operating Revenue by Budget Center
15. REVORG01 - Fund 100
Operating Revenue by Budget Center and Organization
16. REVORG02 - Fund 100
Operating Revenue by Budget Center, Organization and
Account
17. VAREXPORG01 - Fund 100
Operating Expenditures by Budget Center and
Organization –
History
18. AR GURAPAY Dump
19. Daily AP Keyed Activity
20. Grants - Invoice Fund 5
21. EXPORG01NPA - Fund 100
Operating Expenditures by Budget Center and
Organization with
Cascading Prompts
22. REVORG01NPA - Fund 100
Operating Revenue by Budget Center and
Organization with
Cascading Prompts
In
April 2013 the FRS and HRS Plus databases were migrated to ODS. I worked on the FRS databases. I used FOCUS to create Excel spreadsheets
with the data to be loaded. This was
done for each segment in the files, (000, 001, etc.). I gave the spreadsheets to another programmer
who converted them to .csv files, then SQL loaded them to Oracle tables. We loaded detail
data.